import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.SQLException;



public class HUFreq {

	/**
	 * @param args
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 * @throws IOException 
	 */
	public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
		// TODO Auto-generated method stub
		
		BufferedWriter out = new BufferedWriter(new FileWriter("C:\\Users\\Min\\Documents\\Pacbio\\pacbio-FQ-analysis.txt"));
		
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/snpdb","root","");
		
		//Retrieve Counts for total number or Resistant and Sensitive Isolates from DST
		String drug = "Final_RIF";
		double totalNumResistant = 0;
		double totalNumSensitive = 0;
		//PreparedStatement queryDST = con.prepareStatement("SELECT COUNT(*) FROM dst WHERE " + drug + " = ?");
		//PreparedStatement queryDST = con.prepareStatement("SELECT COUNT(*) FROM (SELECT DISTINCT dst.ucsdisolateid, " + drug + " FROM dst, pacbiodata WHERE dst.ucsdisolateid = pacbiodata.ucsdisolateid) as x WHERE " + drug + " = ?");
		PreparedStatement queryDST = con.prepareStatement("SELECT COUNT(*) FROM (SELECT dst.ucsdisolateid, " + drug + " FROM dst, hulist WHERE dst.ucsdisolateid = hulist.rpob AND ucsdisolateid LIKE '1%') as x WHERE " + drug + " = ?");
		
		queryDST.setString(1, "R");
		ResultSet result = queryDST.executeQuery();
		while(result.next()){
			totalNumResistant = result.getInt(1);
		}
		
		queryDST.setString(1, "S");
		ResultSet result1 = queryDST.executeQuery();
		while(result1.next()){
			totalNumSensitive = result1.getInt(1);
		}
		
		//Query PacBio SNPs
		//Calculate Mutation Frequency
		String mutantPosition = null;
		String mutant = null;
		String ref = null;
		PreparedStatement querySNPData = con.prepareStatement("SELECT DISTINCT position, ref, mut FROM rpobsnps WHERE ucsdisolateid LIKE '1%'");
		PreparedStatement queryMutantCount = con.prepareStatement("SELECT * FROM rpobsnps WHERE POSITION = ? and MUT = ? and ucsdisolateid LIKE '1%'");
		PreparedStatement queryMutantDST = con.prepareStatement("SELECT " + drug + " FROM dst WHERE UCSDISOLATEID = ?");
		ResultSet snpdata = querySNPData.executeQuery();
		while(snpdata.next()){
			mutantPosition = snpdata.getString(1);
			ref = snpdata.getString(2);
			mutant = snpdata.getString(3);
			queryMutantCount.setString(1, mutantPosition);
			queryMutantCount.setString(2, mutant);
			ResultSet mutantGroup = queryMutantCount.executeQuery();
			double sumResistantMutants = 0;
			double sumSensitiveMutants = 0;
			String mutantToQueryDst = null;
			String mutantDstQueryResult = null;
			while(mutantGroup.next()){ //Query the total number of isolates that have the same mutation.
				mutantToQueryDst = mutantGroup.getString(1);
				queryMutantDST.setString(1, mutantToQueryDst);
				ResultSet mutantDstResultSet = queryMutantDST.executeQuery();
				
				while(mutantDstResultSet.next()){ //Count the number of Resistant or Sensitive Isolates
					
					if (mutantDstResultSet.getString(1).equals("R")){
						sumResistantMutants = sumResistantMutants + 1;
					}//end of if
					
					if (mutantDstResultSet.getString(1).equals("S")){
						sumSensitiveMutants = sumSensitiveMutants + 1;
					}//end of if
				}//end of while
				
			}
			double resistantFrequency = sumResistantMutants / totalNumResistant;
			double sensitiveFrequency = sumSensitiveMutants / totalNumSensitive;
			
			System.out.println(mutantPosition + "\t" + ref + "\t" + mutant + "\t" + (int) totalNumResistant + "\t" + (int) totalNumSensitive + "\t" + (int) sumResistantMutants + "\t" + (int) sumSensitiveMutants + "\t" + (double)Math.round( resistantFrequency  * 100000) / 100000
 + "\t" +  (double)Math.round(sensitiveFrequency  * 100000) / 100000);
		}
		//System.out.println(totalNumResistant);
		//System.out.println(totalNumSensitive);
		out.close();
	}
	 
}
